package net.sql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

import net.dotool.ConnectionTools;
import net.dotool.StringTools;
import net.sql.config.SQLConfig;
import net.sql.config.SQLFactory;
import net.sql.i.IDbRow;

/**
 * 通用查询模板
 * 
 * @author 李岩飞
 * @email eliyanfei@126.com
 * @date 2018年11月22日下午1:04:48
 */
public class SqlTemplate {
	String dbName;
	String belong;

	public SqlTemplate(String dbName) {
		this.dbName = dbName;
	}

	public SqlTemplate(String belong, String dbName) {
		this.belong = belong;
		this.dbName = dbName;
	}

	public SqlTemplate() {
	}

	/**
	 * 数据统计
	 */
	public int count(String sql, Object... params) {
		Connection conn = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		try {
			if (StringTools.isNotEmpty(belong)) {
				conn = SQLFactory.getConfig().getConnectionByBelong(belong, dbName);
			} else {
				conn = SQLFactory.getConfig().getConnection(dbName);
			}
			st = conn.prepareStatement(sql);
			if (params != null) {
				int idx = 1;
				for (Object obj : params) {
					st.setObject(idx++, obj);
				}
			}
			rs = st.executeQuery();
			while (rs.next()) {
				return rs.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
			SQLConfig.getSqlConfig().printError(e, sql);
		} finally {
			ConnectionTools.closeAll(rs, st, conn);
		}
		return 0;
	}

	/**
	 * 查询数据
	 */
	public void query(String sql, IDbRow dbRow, Object... params) {
		Connection conn = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		try {
			if (StringTools.isNotEmpty(belong)) {
				conn = SQLFactory.getConfig().getConnectionByBelong(belong, dbName);
			} else {
				conn = SQLFactory.getConfig().getConnection(dbName);
			}
			st = conn.prepareStatement(sql);
			if (params != null) {
				int idx = 1;
				for (Object obj : params) {
					st.setObject(idx++, obj);
				}
			}
			rs = st.executeQuery();
			while (rs.next()) {
				dbRow.row(rs);
			}
		} catch (Exception e) {
			e.printStackTrace();
			SQLConfig.getSqlConfig().printError(e, sql);
		} finally {
			ConnectionTools.closeAll(rs, st, conn);
		}
	}

	/**
	 * 提供conn
	 */
	public void query(Connection conn, String sql, IDbRow dbRow, Object... params) {
		PreparedStatement st = null;
		ResultSet rs = null;
		try {
			st = conn.prepareStatement(sql);
			if (params != null) {
				int idx = 1;
				for (Object obj : params) {
					st.setObject(idx++, obj);
				}
			}
			rs = st.executeQuery();
			while (rs.next()) {
				dbRow.row(rs);
			}
		} catch (Exception e) {
			e.printStackTrace();
			SQLConfig.getSqlConfig().printError(e, sql);
		} finally {
			ConnectionTools.closeAll(rs, st, null);
		}
	}

	/**
	 * 操作数据
	 */
	public void execute(String sql, Object... params) throws Exception {
		List<List<Object>> list = new ArrayList<List<Object>>();
		list.add(Arrays.asList(params));
		execute(sql, list);
	}

	public void execute(String sql, List<List<Object>> paramsList) throws Exception {
		Connection conn = null;
		PreparedStatement st = null;
		try {
			if (StringTools.isNotEmpty(belong)) {
				conn = SQLFactory.getConfig().getConnectionByBelong(belong, dbName);
			} else {
				conn = SQLFactory.getConfig().getConnection(dbName);
			}
			st = conn.prepareStatement(sql);
			for (List<Object> params : paramsList) {
				int idx = 1;
				for (Object obj : params) {
					st.setObject(idx++, obj);
				}
				st.addBatch();
			}
			st.executeBatch();
		} catch (Exception e) {
			SQLConfig.getSqlConfig().printError(e, sql);
			throw e;
		} finally {
			ConnectionTools.closeAll(null, st, conn);
		}
	}
}
